package com.hd.controller.sqlexcel;

import com.hd.controller.base.BaseController;
import com.hd.dao.JdbcDao;
import com.hd.entity.sqlexcel.SqlExcelField;
import com.hd.entity.sqlexcel.SqlExcelQueryExpression;
import com.hd.entity.sqlexcel.SqlExcelReport;
import com.hd.entity.sqlexcel.SqlReportType;
import com.hd.service.sqlexcel.SqlExcelFieldService;
import com.hd.service.sqlexcel.SqlExcelQueryExpressionService;
import com.hd.service.sqlexcel.SqlExcelReportService;
import com.hd.service.sqlexcel.SqlReportTypeService;
import com.hd.util.StringUtils;
import com.hd.util.report.sqlexcel.SqlExcelUtil;
import com.hd.util.result.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* sql导出excel报表
*/
@RequestMapping("sqlExcelReport")
@Controller
public class SqlExcelReportController extends BaseController {
    @Autowired
    private SqlExcelReportService sqlExcelReportService;
    @Autowired
    private SqlReportTypeService sqlReportTypeService;
    @Autowired
    private SqlExcelQueryExpressionService sqlExcelQueryExpressionService;
    @Autowired
    private SqlExcelFieldService sqlExcelFieldService;
    @Autowired
    private JdbcDao jdbcDao;
    @Autowired
    private SqlExcelUtil sqlExcelUtil;
    /**
     * 主页
     * @return
     */
    @RequestMapping("manager")
    public String manager(Model model) {
    	  //查询报表类型
    	List<SqlReportType> types = sqlReportTypeService.findAll();
    	model.addAttribute("types", types);
        return "jsp/report/sqlExcelReport/sqlExcelReportList";
    }
    /**
     * 列表
     * @param page
     * @param rows
     * @param sort
     * @param order
     * @return
     */
    @RequestMapping(value="/dataGrid",method=RequestMethod.POST)
    @ResponseBody
    public Object dataGrid(SqlExcelReport sqlExcelReport, Integer page, Integer rows, String sort, String order) {
        PageInfo pageInfo = new PageInfo(page, rows, sort, order);
        Map<String,Object>condition=new HashMap<String,Object>();
        if(StringUtils.isNotNull(sqlExcelReport.getName())) {
        	condition.put("name", sqlExcelReport.getName());
        }
        if(StringUtils.isNotNull(sqlExcelReport.getType())) {
        	condition.put("type", sqlExcelReport.getType());
        }
        pageInfo.setCondition(condition);
        sqlExcelReportService.selectDataGrid(pageInfo);
        return pageInfo;
    }

    /**
     * 添加页
     * @return
     */
    @RequestMapping("/addPage")
    public String addPage(Model model) {
    	//查询报表类型
    	List<SqlReportType> types = sqlReportTypeService.findAll();
    	model.addAttribute("types", types);
        return "jsp/report/sqlExcelReport/sqlExcelReportAdd";
    }

    /**
     * 添加
     * @param sqlExcelReport
     * @return
     */
    @RequestMapping(value="/add",method=RequestMethod.POST)
    @ResponseBody
    public Object add(SqlExcelReport sqlExcelReport) {
        sqlExcelReportService.save(sqlExcelReport);
        return renderSuccess("添加成功！");
    }

    /**
     * 删除
     * @param id
     * @return
     */
    @RequestMapping(value = "/delete/{id}",method = RequestMethod.POST)
    @ResponseBody
    public Object delete(@PathVariable("id") String id) {
//        sqlExcelReportService.delete(id);
        sqlExcelReportService.updateStatus(0,id);
        return renderSuccess("删除成功！");
    }
    /**
     * 编辑页
     * @param model
     * @param id
     * @return
     */
    @RequestMapping("/editPage/{id}")
	public String editPage(Model model, @PathVariable("id")String id) {
	 SqlExcelReport sqlExcelReport = sqlExcelReportService.get(id);
        model.addAttribute("sqlExcelReport", sqlExcelReport);
        //查询报表类型
    	List<SqlReportType> types = sqlReportTypeService.findAll();
    	model.addAttribute("types", types);
        return "jsp/report/sqlExcelReport/sqlExcelReportEdit";
    }

    /**
     * 查看页
     * @param model
     * @param id
     * @return
     */
    @RequestMapping("/viewPage/{id}")
    public String viewPage(Model model, @PathVariable("id") String id) {
        SqlExcelReport sqlExcelReport = sqlExcelReportService.get(id);
        model.addAttribute("sqlExcelReport", sqlExcelReport);
        return "jsp/report/sqlExcelReport/sqlExcelReportView";
    }
    /**
     * 编辑
     * @param sqlExcelReport
     * @return
     */
    @RequestMapping(value = "/edit",method = RequestMethod.POST)
    @ResponseBody
    public Object edit(SqlExcelReport sqlExcelReport) {
    	 SqlExcelReport vo = sqlExcelReportService.get(sqlExcelReport.getId());
    	 sqlExcelReport.setSql(vo.getSql());
        sqlExcelReportService.update(sqlExcelReport);
        return renderSuccess("编辑成功！");
    }


    /**
     * 获取查询参数
     * @param fid
     * @return
     */
    public Map<String,String>getSqlParamExpression(String fid){
        Map<String,String>params=new HashMap<String,String>();
        List<SqlExcelQueryExpression>sqlExcelQueryExpressions=sqlExcelQueryExpressionService.findAllByFId(fid);
        if(sqlExcelQueryExpressions.size()>0){
            for(SqlExcelQueryExpression e:sqlExcelQueryExpressions){
                String value=e.getExpression();
                params.put(e.getPlaceholder(),value);
            }
        }
        return params;
    }

    public String[] getSerchSql(String id){
        String s[]=new String[2];
        SqlExcelReport sqlExcelReport=sqlExcelReportService.get(id);
        String sql=sqlExcelReport.getSql();//获取查询sql
        s[0]=sql;
        s[1]=sqlExcelReport.getDbType();
        return s;
    }



    @ResponseBody
    @RequestMapping(value = "/searchData",method = RequestMethod.POST)
    public Object searchData(Integer page,Integer rows,String reportid,HttpServletRequest request){
        String mysqlDB="mysql";
        Map<String,Object>paramsMap=new HashMap<String,Object>();
        List<SqlExcelQueryExpression>sqlExcelQueryExpressions=sqlExcelQueryExpressionService.findAllByFId(reportid);
        String s[]=getSerchSql(reportid);
        String sql=s[0];
        String dbType=s[1];
        System.out.println(sql);
        if(sqlExcelQueryExpressions.size()>0){
            for(SqlExcelQueryExpression e:sqlExcelQueryExpressions){
                String paramName="";
                String paramNameDateStart="";
                String paramNameDateEnd="";
                if(e.getType().equals("String")){
                    paramName=request.getParameter(e.getKey());
                    if(StringUtils.isNotNull(paramName)){
                       String expression = e.getExpression();
                       expression = expression.replace(":" + e.getKey(), paramName);
                       sql = sql.replace(e.getPlaceholder(), expression);
                   }else{
                       sql=sql.replace(e.getPlaceholder()," ");
                   }
                }
                if(e.getType().equals("Date")){
                    paramNameDateStart=request.getParameter(e.getKey()+"Start");
                    paramNameDateEnd=request.getParameter(e.getKey()+"End");
                    String expression="";
                    boolean b=false;
                    if(StringUtils.isNotNull(paramNameDateStart)){
                        paramsMap.put(e.getKey(),paramNameDateStart);
                        if(mysqlDB.equals(dbType)) {
                            expression += " and " + e.getKey() + " >=STR_TO_DATE('" + paramNameDateStart + "','%Y-%m-%d')";
                        }
                        b=true;
                    }
                    if(StringUtils.isNotNull(paramNameDateEnd)){
                        paramsMap.put(e.getKey(),paramNameDateEnd);
                        if(mysqlDB.equals(dbType)) {
                            expression += " and " + e.getKey() + " <=STR_TO_DATE('" + paramNameDateEnd + "','%Y-%m-%d')";
                        }
                        b=true;
                    }
                    if(b) {
                        sql = sql.replace(e.getPlaceholder(), expression);
                    }else{
                        sql=sql.replace(e.getPlaceholder()," ");
                    }
                }
            }
        }
        String countSql=createCountSql(sql);
        int total=jdbcDao.getCount(countSql,paramsMap);
        if(mysqlDB.equals(dbType)) {
            sql += " limit " + (page - 1) * rows + "," + rows;
        }
        List<Map<String, Object>>datas=jdbcDao.findAll(sql,paramsMap);
        PageInfo info=new PageInfo(page,rows);
        info.setRows(datas);
        info.setTotal(total);
        return info;
    }

    @RequestMapping("/updateSqlExcelQuerySql")
    @ResponseBody
    public Object updateSqlExcelQuerySql(String id,String querySql){
        String sql=querySql;
        try {
            for (int i = 0; i < 20; i++) {
                if (querySql.contains("#" + i)) {
                    querySql = querySql.replace("#" + i, "");
                }
            }
            querySql = createCountSql(querySql);
            jdbcDao.getCount(querySql, new HashMap<String, Object>());
        }catch (Exception ee){
            ee.printStackTrace();
            return renderError("解析SQL语句错误！");
        }
        SqlExcelReport vo=sqlExcelReportService.get(id);
        vo.setSql(sql);
        sqlExcelReportService.update(vo);
        return renderSuccess("保存成功！");
    }

    protected  String createCountSql(String sql){
        String newSql="select count(*) ";
        int index=sql.indexOf("from");
        if(index==-1){
            index=sql.indexOf("FROM");
        }
        newSql+=sql.substring(index,sql.length());
        return newSql;
    }

    @RequestMapping("/view")
    public String view(String id,Model model){
        List<SqlExcelField>fields=sqlExcelFieldService.findAllByFId(id);
        List<SqlExcelQueryExpression>expressions=sqlExcelQueryExpressionService.findAllByFId(id);
        model.addAttribute("expressions",expressions);
        model.addAttribute("fields",fields);
        model.addAttribute("id",id);
        return "jsp/report/reportList";
    }

    @RequestMapping("/export")
    public void exportReport(String reportid, HttpServletRequest request, HttpServletResponse response)throws Exception{
        SqlExcelReport report=sqlExcelReportService.get(reportid);
        String mysqlDB="mysql";
        Map<String,Object>paramsMap=new HashMap<String,Object>();
        List<SqlExcelQueryExpression>sqlExcelQueryExpressions=sqlExcelQueryExpressionService.findAllByFId(reportid);
        String s[]=getSerchSql(reportid);
        String sql=s[0];
        String dbType=s[1];
        if(sqlExcelQueryExpressions.size()>0){
            for(SqlExcelQueryExpression e:sqlExcelQueryExpressions){
                String paramName="";
                String paramNameDateStart="";
                String paramNameDateEnd="";
                if(e.getType().equals("String")){
                    paramName=request.getParameter(e.getKey());
                    if(StringUtils.isNotNull(paramName)){
                        String expression = e.getExpression();
                        expression = expression.replace(":" + e.getKey(), paramName);
                        sql = sql.replace(e.getPlaceholder(), expression);
                    }else{
                        sql=sql.replace(e.getPlaceholder()," ");
                    }
                }
                if(e.getType().equals("Date")){
                    paramNameDateStart=request.getParameter(e.getKey()+"Start");
                    paramNameDateEnd=request.getParameter(e.getKey()+"End");
                    String expression="";
                    boolean b=false;
                    if(StringUtils.isNotNull(paramNameDateStart)){
                        paramsMap.put(e.getKey(),paramNameDateStart);
                        if(mysqlDB.equals(dbType)) {
                            expression += " and " + e.getKey() + " >=STR_TO_DATE('" + paramNameDateStart + "','%Y-%m-%d')";
                        }
                        b=true;
                    }
                    if(StringUtils.isNotNull(paramNameDateEnd)){
                        paramsMap.put(e.getKey(),paramNameDateEnd);
                        if(mysqlDB.equals(dbType)) {
                            expression += " and " + e.getKey() + " <=STR_TO_DATE('" + paramNameDateEnd + "','%Y-%m-%d')";
                        }
                        b=true;
                    }
                    if(b) {
                        sql = sql.replace(e.getPlaceholder(), expression);
                    }else{
                        sql=sql.replace(e.getPlaceholder()," ");
                    }
                }
            }
        }

        Map<String, Object> paramMap = new HashMap<String, Object>();
        Map<String, String> columnMap = sqlExcelUtil.getColumnMap(reportid);
        String basePath=request.getServletContext().getRealPath("/upload/report");
        File baseFile=new File(basePath);
        if(!baseFile.exists()){
            baseFile.mkdirs();
        }
        String path =basePath + File.separator+report.getName()+".xls";
        File file=new File(path);
        String title =report.getName();
        sqlExcelUtil.generatorExcelBySql(sql, paramMap, columnMap, path, title);
        download(report.getName()+".xls",path,request,response);
        file.delete();
    }

}
